package com.boss.thirteen.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.entity.AgencySalaryPayment;
import com.boss.sys.ca.user.dto.UserDTO;
import com.boss.sys.utils.SysUtils;
import com.boss.thirteen.dao.IThirteenSalaryPaymentDao;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class ThirteenSalaryPaymentDaoImpl extends BaseDaoImpl implements IThirteenSalaryPaymentDao {

    @Override
    public List<AgencySalaryPayment> queryAgencySalaryPaymentPage(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        List<Object> sqlParams = new ArrayList<>(1);
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" asp.id, sb.salary_name salaryBaseName, asp.is_using, asp.bgt_id, sbi.person_state personType, sbd.`name` personStateName, ")
                .append(" sbi.salary_base_id salaryId, sbi.id salaryItemizeId, sb.salary_code column_name, ")
                .append(" ai.id agencyId, ai.agency_code, ai.agency_name, asp.salary_year year ");
        sql.append(" FROM ");
        sql.append(TableName.AGENCY_INFO).append(" ai ");
               // .append(" JOIN ").append(TableName.AGENCY_SALARY).append(" asl ")
                //.append(" ON asl.agency_id = ai.id ");

        sql.append(" JOIN ").append(TableName.THIRTEEN_SALARY_ITEMIZE).append(" sbi ")
                .append(" ON  sbi.agency_id = ai.id ");
        sql.append(" JOIN ").append(TableName.SALARY_BASEDATA).append(" sb ")
                .append(" ON sbi.salary_base_id = sb.id");
        sql.append(" LEFT JOIN ").append(TableName.THIRTEEN_SALARY_BGT).append(" asp")
                .append(" ON ( sbi.salary_base_id = asp.salary_id AND asp.agency_id = ai.id")
                .append(" and sbi.person_state=asp.person_type and  asp.is_using=1  and asp.is_deleted=").append(SystemDictConstant.NOT_DELETED).append(") ");
        sql.append(" JOIN ").append(TableName.BASE_DATA).append(" sbd ")
                .append(" ON sbi.person_state = sbd.code and sbd.type = 'salary_type' and sbd.mof_div_code = ?");
        sql.append(" WHERE ")
                .append(" sbi.agency_id = ? ");
        sql.append(" and sbi.is_deleted= ").append(SystemDictConstant.NOT_DELETED);
        sql.append(" ORDER BY  sbi.person_state , sb.show_order,salary_base_id ");
        sqlParams.add(queryParam.get("mof_div_code"));
        sqlParams.add(queryParam.get("agencyId"));
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(sqlParams.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(AgencySalaryPayment.class), params);
    }

    @Override
    public int queryAgencySalaryPaymentTotal(Map<String, Object> queryParam) {
        List<Object> sqlParams = new ArrayList<>(1);
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" COUNT(*) ");
        sql.append(" FROM ");
        sql.append(TableName.AGENCY_INFO).append(" ai ");
        sql.append(" JOIN ").append(TableName.THIRTEEN_SALARY_ITEMIZE).append(" sbi ")
                .append(" ON  sbi.agency_id = ai.id ");
        sql.append(" JOIN ").append(TableName.SALARY_BASEDATA).append(" sb ")
                .append(" ON sbi.salary_base_id = sb.id");
        sql.append(" LEFT JOIN ").append(TableName.THIRTEEN_SALARY_BGT).append(" asp")
                .append(" ON (sbi.salary_base_id = asp.salary_id AND asp.agency_id = ai.id ")
                .append("and sbi.person_state=asp.person_type and  asp.is_using=1 and asp.is_deleted=").append(SystemDictConstant.NOT_DELETED).append(") ");
        sql.append(" JOIN ").append(TableName.BASE_DATA).append(" sbd ")
                .append(" ON sbi.person_state = sbd.code and sbd.type = 'salary_type' and sbd.mof_div_code = ?");
        sql.append(" WHERE ")
                .append(" sbi.agency_id = ? ");
        sql.append(" and sbi.is_deleted= ").append(SystemDictConstant.NOT_DELETED);
        sql.append(" ORDER BY salary_base_id, sbi.person_state DESC ");
        sqlParams.add(queryParam.get("mof_div_code"));
        sqlParams.add(queryParam.get("agencyId"));
        return this.getJdbcTemplate().queryForObject(sql.toString(), sqlParams.toArray(), Integer.class);
    }

    @Override
    public void deleteHookUpData(String salaryId, String personType, String salary_year, String province, String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from ").append(TableName.THIRTEEN_SALARY_BGT);
        sql.append(" where salary_id = ? and person_type = ? and salary_year = ? and province = ? and agency_id = ? ");
        Object[] objects = new Object[] {
          salaryId, personType, salary_year, province, agencyId
        };
        this.getJdbcTemplate().update(sql.toString(), objects);
    }
    @Override
    public void deleteHookUpDataAll( String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.THIRTEEN_SALARY_BGT);
        sql.append(" set update_time=?,is_deleted=? where   province = ? ");
        Object[] objects = new Object[] {
                String.valueOf(DateUtils.getCurrentTimeStamp()),SystemDictConstant.IS_DELETED, province
        };
        this.getJdbcTemplate().update(sql.toString(), objects);
    }
    @Override
    public void deleteHookUpDataByBgtId(String salaryId, String personType, String salary_year, String province,List<String> bgtId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from ").append(TableName.THIRTEEN_SALARY_BGT);
        sql.append(" where salary_id = ? and person_type = ? and salary_year = ? and province = ? ").append(" and ").append(SysUtils.inSql("bgt_id",bgtId));
        Object[] objects = new Object[] {
                salaryId, personType, salary_year, province
        };
        this.getJdbcTemplate().update(sql.toString(), objects);
    }

    @Override
    public List<AgencySalaryPayment> getHookUpData(String salaryId, String personType, String salary_year, String province, String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" asp.id, asp.biz_key, asp.billguid, asp.create_time, asp.update_time, asp.salary_year year, asp.province,  ")
                .append(" asp.agency_id, asp.person_type, asp.salary_id, asp.agency_salary_id, asp.salary_itemize_id, ")
                .append(" asp.bgt_id, asp.salary_code column_name, asp.is_using, asp.pay_order, asp.is_deleted, ai.agency_code, ai.agency_name ");
        sql.append(" from ").append(TableName.THIRTEEN_SALARY_BGT).append(" asp");
        sql.append(" JOIN ").append(TableName.AGENCY_INFO).append(" ai ")
                .append(" ON asp.agency_id = ai.id ");
        sql.append(" where asp.salary_id = ? and asp.person_type = ? and asp.salary_year = ? and asp.province = ? and asp.agency_id = ? and asp.is_deleted=? ");
        Object[] objects = new Object[] {
                salaryId, personType, salary_year, province, agencyId,SystemDictConstant.NOT_DELETED
        };
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencySalaryPayment.class), objects);
    }

    @Override
    public void saveHookUpData(List<Object[]> params) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.THIRTEEN_SALARY_BGT);
        sql.append("(id, biz_key, billguid, create_time, update_time, salary_year, province, agency_id, person_type, ")
                .append(" salary_id, agency_salary_id, salary_itemize_id, bgt_id, salary_code, is_using, ")
                .append(" pay_order, is_deleted ,mof_div_code) ");
        sql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 18; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });

    }

    @Override
    public List<AgencySalaryPayment> getBgtBySalaryPersonType(String salaryId, String meritPayCode, String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" id, biz_key, billguid, create_time, update_time, salary_year year, province, agency_id, person_type, salary_id, ")
                .append(" agency_salary_id, salary_itemize_id, bgt_id, salary_code column_name, is_using, pay_order, is_deleted ");
        sql.append(" from ").append(TableName.THIRTEEN_SALARY_BGT);
        sql.append(" where salary_id = ? and person_type = ? and agency_id = ? and  is_using=1 and is_deleted=? ");
        Object[] objects = new Object[]{
          salaryId, meritPayCode, agencyId,SystemDictConstant.NOT_DELETED
        };
        return this.getjdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper<>(AgencySalaryPayment.class), objects);

    }

    @Override
    public List<AgencySalaryPayment> getBgtBySalaryPersonType(List<String> agencyIds, UserDTO userDto) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" id, biz_key, billguid, create_time, update_time, salary_year year, province, agency_id, person_type, salary_id, ")
                .append(" agency_salary_id, salary_itemize_id, bgt_id, salary_code column_name, is_using, pay_order, is_deleted ");
        sql.append(" from ").append(TableName.THIRTEEN_SALARY_BGT);
        sql.append(" where is_using=1 and is_deleted=0 and ").append(SysUtils.inSql("agency_id",agencyIds))
        .append("  and salary_year=? and  mof_div_code=?")
        .append(" order by agency_id,salary_id,person_type,pay_order ");
        Object[] objects = new Object[]{
                userDto.getYear(),userDto.getProvince()
        };
        return this.getjdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper<>(AgencySalaryPayment.class), objects);

    }

    @Override
    public List<AgencySalaryPayment> getPaymentLock(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" id, agency_id, bgt_id, payee_acct_name, payee_acct_no, payee_acct_bank_name, amt, is_lock, is_deleted, mof_div_code ");
        sql.append(" from ").append(TableName.PAY_MENT_LOCK);
        sql.append(" where agency_id = ? and  is_lock=1 and is_deleted=0 ");

        return this.getjdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper<>(AgencySalaryPayment.class), agencyId);
    }
}
